Step 3: Relationships Explained — WhatsApp

Relationship Map Overview

The WhatsApp data model has 23 distinct relationships spanning user identity, messaging, calling, and ephemeral content domains.

1. User-Centric Relationships

users → user_settings (1:1)

Each user has exactly one settings row controlling privacy and notifications.

users.user_id ←→ user_settings.user_id

Cardinality: One user, one settings record. Created automatically on registration.

users → user_contacts (1:N — bidirectional, asymmetric)

A user can have many saved contacts, and a user can appear in many others' contact lists.

users.user_id ←→ user_contacts.owner_user_id    (contacts I have saved)
users.user_id ←→ user_contacts.contact_user_id  (people who have me saved)

Cardinality: Each user can save thousands of contacts. The relationship is asymmetric — A can have B saved without B having A.

Why this matters:

• Privacy enforcement: "My Contacts" visibility checks whether the viewer has the poster's number saved
• Broadcast delivery: messages only reach recipients who have the sender saved

-- Check if user B can see user A's last_seen (A's setting = 'my_contacts')
SELECT 1 FROM user_contacts
WHERE owner_user_id = 'user_B' AND contact_user_id = 'user_A';

users → user_blocks (1:N)

A user can block multiple users. Blocking is unidirectional.

users.user_id ←→ user_blocks.blocker_user_id  (blocks I created)
users.user_id ←→ user_blocks.blocked_user_id  (people who blocked me)

Enforcement pattern:

-- Before delivering a message from sender to recipient:
SELECT 1 FROM user_blocks
WHERE blocker_user_id = :recipient AND blocked_user_id = :sender;
-- If row exists → silently drop the message

users → device_sessions (1:N)

A user can have up to 5 linked devices (1 primary + 4 companions).

users.user_id ←→ device_sessions.user_id

Cardinality: 1 user : 1-5 device sessions. Each device has its own encryption keys.

2. Chat & Group Relationships

users → chats (N:M via group_members)

Users participate in multiple chats, and each chat has multiple participants.

users.user_id ←→ group_members.user_id ←→ group_members.chat_id ←→ chats.chat_id

Cardinality: A user can be in thousands of chats. A group chat can have up to 1024 members.

chats → group_settings (1:1)

Each group chat has exactly one configuration row.

chats.chat_id ←→ group_settings.chat_id

Constraint: Only applicable when chats.chat_type = 'group'

chats → group_invite_links (1:N)

A group can have multiple invite links (generated by different admins).

chats.chat_id ←→ group_invite_links.chat_id

Business logic:

-- Validate an invite link before allowing a join
SELECT * FROM group_invite_links
WHERE invite_code = :code
  AND is_revoked = FALSE
  AND (expires_at IS NULL OR expires_at > NOW())
  AND (max_uses IS NULL OR use_count < max_uses);

3. Messaging Relationships

chats → messages (1:N)

Each chat contains many messages, ordered by sent_at.

chats.chat_id ←→ messages.chat_id

Cardinality: A single group chat can accumulate millions of messages over time.

messages → messages (Self-Referencing 1:N — Reply Threading)

A message can be a reply to another message in the same chat.

messages.message_id ←→ messages.reply_to_message_id

Implementation:

-- Get a message along with the message it's replying to
SELECT m.*, 
       r.encrypted_content AS replied_to_content,
       r.sender_id AS replied_to_sender
FROM messages m
LEFT JOIN messages r ON m.reply_to_message_id = r.message_id
WHERE m.chat_id = :chat_id
ORDER BY m.sent_at DESC
LIMIT 50;

messages → message_status (1:N)

Each message has one delivery-status row per recipient.

messages.message_id ←→ message_status.message_id

Cardinality: For a 1:1 chat, 1 status row per message. For a 500-member group, 500 rows per message.

Critical query — aggregate message status for sender:

-- Get the "worst" status for a group message (determines which tick to show)
SELECT 
    CASE 
        WHEN COUNT(*) FILTER (WHERE status = 'sent') > 0 THEN 'sent'
        WHEN COUNT(*) FILTER (WHERE status = 'delivered') > 0 THEN 'delivered'
        ELSE 'read'
    END AS aggregate_status
FROM message_status
WHERE message_id = :msg_id;

messages → message_reactions (1:N)

A message can receive multiple emoji reactions from different users.

messages.message_id ←→ message_reactions.message_id

Constraint: One reaction per user per message (UNIQUE on message_id, user_id)

messages → media_attachments (1:N)

A message can have one or more attached media files.

messages.message_id ←→ media_attachments.message_id

Cardinality: Typically 1:1, but some messages can have multiple images.

4. Call Relationships

chats → calls (1:N)

Calls are initiated within a chat context.

chats.chat_id ←→ calls.chat_id

calls → call_participants (1:N)

Each call has multiple participants (2 for 1:1, up to 32 for group calls).

calls.call_id ←→ call_participants.call_id

Query — call history with participant names:

SELECT c.call_id, c.call_type, c.status, c.duration_seconds,
       u.display_name AS participant_name, cp.status AS participant_status
FROM calls c
JOIN call_participants cp ON c.call_id = cp.call_id
JOIN users u ON cp.user_id = u.user_id
WHERE c.caller_id = :user_id OR cp.user_id = :user_id
ORDER BY c.started_at DESC;

5. Status (Stories) Relationships

users → status_updates (1:N)

A user can post multiple statuses (up to 30 per 24-hour window).

users.user_id ←→ status_updates.user_id

status_updates → status_viewers (1:N)

Each status tracks who viewed it.

status_updates.status_id ←→ status_viewers.status_id

Fan-out-on-read pattern — get contacts' statuses:

-- Get status feed for a user (only from contacts who have them saved back)
SELECT su.*
FROM status_updates su
JOIN user_contacts uc ON su.user_id = uc.contact_user_id
WHERE uc.owner_user_id = :current_user
  AND su.expires_at > NOW()
  AND NOT EXISTS (
      SELECT 1 FROM user_blocks 
      WHERE blocker_user_id = :current_user AND blocked_user_id = su.user_id
  )
ORDER BY su.posted_at DESC;

6. Broadcast Relationships

users → broadcast_lists (1:N)

A user can create multiple broadcast lists.

users.user_id ←→ broadcast_lists.owner_id

broadcast_lists → broadcast_recipients (1:N)

Each broadcast list contains multiple recipients.

broadcast_lists.broadcast_id ←→ broadcast_recipients.broadcast_id

Max: 256 recipients per broadcast list

Relationship Summary Table

High-Volume Considerations

message_status — The Heaviest Table

At WhatsApp's scale (100B messages/day), with an average group size of 20, this table could see 2 trillion writes per day. Strategies:

• Partition by message_id range (time-bucketed)
• TTL-based cleanup (delete status rows after 30 days)
• Write-behind caching (batch status updates, flush periodically)

status_viewers — Fan-Out-on-Read

Instead of writing a "status available" row for every contact when a status is posted (fan-out-on-write), the system reads the contact graph at query time. This trades read latency for write efficiency.

This content is reserved for Premium Members.